import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('recordings.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);
    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE recordings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT NOT NULL,
        name TEXT NOT NULL,
        path TEXT NOT NULL,
        duration INTEGER NOT NULL,
        size INTEGER NOT NULL,
        create_time INTEGER NOT NULL
      )
    ''');
  }

  // 插入录音记录
  Future<int> insertRecording(Map<String, dynamic> data) async {
    final db = await instance.database;
    return await db.insert('recordings', data);
  }

  // 删除录音记录
  Future<int> deleteRecording(int id) async {
    final db = await instance.database;
    return await db.delete(
      'recordings',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 更新录音记录
  Future<int> updateRecording(int id, Map<String, dynamic> data) async {
    final db = await instance.database;
    return await db.update(
      'recordings',
      data,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 查询所有录音记录（支持排序和分页）
  Future<List<Map<String, dynamic>>> fetchRecordings({
    String? orderBy = 'create_time DESC', // 默认按创建时间降序排序
    int? limit, // 分页条数
    int? offset, // 分页偏移量
  }) async {
    final db = await instance.database;

    // 构造排序和分页的 SQL 查询
    String limitClause = '';
    if (limit != null) {
      limitClause = 'LIMIT $limit';
      if (offset != null) {
        limitClause += ' OFFSET $offset';
      }
    }

    return await db.query(
      'recordings',
      orderBy: orderBy,
      limit: limit,
      offset: offset,
    );
  }

  // 根据用户 ID 查询录音记录（可选排序和分页）
  Future<List<Map<String, dynamic>>> fetchRecordingsByUser({
    required String userId,
    String? orderBy = 'create_time DESC',
    int? limit,
    int? offset,
  }) async {
    final db = await instance.database;

    return await db.query(
      'recordings',
      where: 'user_id = ?',
      whereArgs: [userId],
      orderBy: orderBy,
      limit: limit,
      offset: offset,
    );
  }

  // 清空录音表（删除所有数据）
  Future<int> clearAllRecordings() async {
    final db = await instance.database;
    return await db.delete('recordings');
  }
}
